﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBkp_LogAll]
#-- Purpose:		Calls spBkp_Log for all databases.
#--	Last Update:	02/07/2014
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	None
#-- Readings:		http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBkp_LogAll]
(
	@verify			bit					= 1,
	@label			nvarchar(128)		= NULL
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@startTime datetime, @iCount int, @sError nvarchar(4000), @db_name sysname,
		@bkp_script nvarchar(MAX)

--- Initialize the start date 
SELECT	@startTime = GETDATE()

--- Run the pre-script, if there is one
IF EXISTS (SELECT TOP 1 [cfg_bkp_prescript] FROM [dbo].[tblMaint_Config] WHERE [cfg_bkp_prescript] IS NOT NULL)
  BEGIN
	SELECT	@bkp_script = LTRIM(RTRIM([cfg_bkp_prescript]))
	FROM	[dbo].[tblMaint_Config]
	WHERE	[cfg_bkp_prescript] IS NOT NULL

	IF @bkp_script <> SPACE(0)
		EXEC	(@bkp_script)
  END

--- Execute the command
DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
	SELECT		db.name
	FROM		[master].[sys].[databases] db
	JOIN		[master].[sys].[master_files] mf ON db.database_id = mf.database_id
				AND mf.type = 0
	WHERE		db.name NOT IN (SELECT database_name FROM [dbo].[vwMaint_DoNotBackup])
	GROUP BY	db.name
	ORDER BY	SUM(mf.size)
OPEN crs
FETCH NEXT FROM crs INTO @db_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	EXEC [dbo].[spBkp_Log] @database_name = @db_name, @verify = @verify, @label = @label

	FETCH NEXT FROM crs INTO @db_name
  END
CLOSE crs
DEALLOCATE crs

--- Run the post-script, if there is one
IF EXISTS (SELECT TOP 1 [cfg_bkp_postscript] FROM [dbo].[tblMaint_Config] WHERE [cfg_bkp_postscript] IS NOT NULL)
  BEGIN
	SELECT	@bkp_script = LTRIM(RTRIM([cfg_bkp_postscript]))
	FROM	[dbo].[tblMaint_Config]
	WHERE	[cfg_bkp_postscript] IS NOT NULL

	IF @bkp_script <> SPACE(0)
		EXEC	(@bkp_script)
  END

--- Look for any errors since the start time
SELECT	@iCount = COUNT(*) FROM [dbo].[tblMaint_Log] WHERE mnt_action_type = 'backup_log' AND mnt_success = 0 AND mnt_action_date >= @startTime
IF (@iCount > 0)
  BEGIN
	--- When there were errors, raise an error so an event is fired
	SELECT	@sError = COALESCE(@sError + ', ', '') + mnt_table_catalog FROM [dbo].[tblMaint_Log] WHERE mnt_action_type = 'backup_log' AND mnt_success = 0 AND mnt_action_date >= @startTime
	SELECT	@sError = 'The following databases had failures during transaction log backups: ' + ISNULL(@sError, 'N/A')

	RAISERROR(@sError, 16, 1)
  END

SET NOCOUNT OFF
